package com.etc.baolema.dao.impl;

import com.etc.baolema.dao.StoreDAO;
import com.etc.baolema.entity.Pager;
import com.etc.baolema.entity.Store;
import com.etc.baolema.util.DataSourceUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StoreDAOImpl implements StoreDAO {
    @Override
    public int ADDStore(Store store) {
        String sql = "INSERT INTO tab_store(sid,sname,stype,suser,spwd,saddress,phone,sdate) VALUES(NULL,?,?,?,?,?,?,now())";
        int result = -1;
        Connection conn = null;
        PreparedStatement psmt = null;
        try {
            //获取Connection对象
            conn = DataSourceUtil.getConnection();
            //获取预处理对象 PreparedStatement
            psmt = conn.prepareStatement(sql);
            //如果SQL中有占位符，给占位符赋值，没有过。
            psmt.setString(1, store.getSname());
            psmt.setString(2, store.getStype());
            psmt.setString(3, store.getSuser());
            psmt.setString(4, store.getSpwd());
            psmt.setString(5, store.getSaddress());
            psmt.setString(6, store.getPhone());
            //执行SQL: 查询- executeQuery, 增删改- executeUpdate() : int 影响行数;
            result = psmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataSourceUtil.releaseResource(psmt, conn);
        }
        return result;
    }

    @Override
    public Store login(String suser) {
        Store store = null;
        String sql = "SELECT * FROM tab_store WHERE suser = ?";
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            conn = DataSourceUtil.getConnection();
            psmt = conn.prepareStatement(sql);
            psmt.setString(1, suser);

            rs = psmt.executeQuery();
            if (rs.next()) {
                store = new Store();
                store.setSid(rs.getInt(1));
                store.setSname(rs.getString(2));
                store.setStype(rs.getString(3));
                store.setSuser(rs.getString(4));
                store.setSpwd(rs.getString(5));
                store.setSaddress(rs.getString(6));
                store.setPhone(rs.getString(7));
                store.setSdate(rs.getString(8));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataSourceUtil.releaseResource(rs, psmt, conn);
        }

        return store;
    }

    @Override
    public Store checkSname(String sname) {
        Store store = null;
        String sql = "SELECT * FROM tab_store WHERE sname = ?";
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            conn = DataSourceUtil.getConnection();
            psmt = conn.prepareStatement(sql);
            psmt.setString(1, sname);

            rs = psmt.executeQuery();
            if (rs.next()) {
                store = new Store();
                store.setSid(rs.getInt(1));
                store.setSname(rs.getString(2));
                store.setStype(rs.getString(3));
                store.setSuser(rs.getString(4));
                store.setSpwd(rs.getString(5));
                store.setSaddress(rs.getString(6));
                store.setPhone(rs.getString(7));
                store.setSdate(rs.getString(8));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataSourceUtil.releaseResource(rs, psmt, conn);
        }

        return store;
    }

    @Override
    public List<Store> queryAllStore() {
        List<Store> list = new ArrayList<>();
        Store store=null;
        String sql = "SELECT * FROM tab_store ";
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            conn = DataSourceUtil.getConnection();
            psmt = conn.prepareStatement(sql);
            rs = psmt.executeQuery();
            while (rs.next()) {
                store = new Store();
                store.setSid(rs.getInt(1));
                store.setSname(rs.getString(2));
                store.setStype(rs.getString(3));
                store.setSuser(rs.getString(4));
                store.setSpwd(rs.getString(5));
                store.setSaddress(rs.getString(6));
                store.setPhone(rs.getString(7));
                store.setSdate(rs.getString(8));
                list.add(store);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataSourceUtil.releaseResource(rs, psmt, conn);
        }
        return list;
    }

    @Override
    public List<Store> queryAllStoreByPage(Pager<Store> page) {
        List<Store> list = new ArrayList<>();
        Store store=null;
        String sql = "SELECT * FROM tab_store limit ?,?";
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            conn = DataSourceUtil.getConnection();
            psmt = conn.prepareStatement(sql);
            psmt.setInt(1,page.getStart());
            psmt.setInt(2,page.getEnd());
            rs = psmt.executeQuery();
            while (rs.next()) {
                store = new Store();
                store.setSid(rs.getInt(1));
                store.setSname(rs.getString(2));
                store.setStype(rs.getString(3));
                store.setSuser(rs.getString(4));
                store.setSpwd(rs.getString(5));
                store.setSaddress(rs.getString(6));
                store.setPhone(rs.getString(7));
                store.setSdate(rs.getString(8));
                list.add(store);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataSourceUtil.releaseResource(rs, psmt, conn);
        }
        return list;
    }

    @Override
    public List<Store> queryBysname(String sname) {
        List<Store> list = new ArrayList<>();
        Store store=null;
        String sql = "SELECT * FROM tab_store where 1=1 ";
        if(sname!=null&&sname!=""){
            sql +="and sname like concat('%',?,'%')";
        }
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            conn = DataSourceUtil.getConnection();
            psmt = conn.prepareStatement(sql);
            if(sname!=null&&sname!=""){
                psmt.setString(1,sname);
            }
            rs = psmt.executeQuery();
            while (rs.next()) {
                store = new Store();
                store.setSid(rs.getInt(1));
                store.setSname(rs.getString(2));
                store.setStype(rs.getString(3));
                store.setSuser(rs.getString(4));
                store.setSpwd(rs.getString(5));
                store.setSaddress(rs.getString(6));
                store.setPhone(rs.getString(7));
                store.setSdate(rs.getString(8));
                list.add(store);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataSourceUtil.releaseResource(rs, psmt, conn);
        }
        return list;
    }

    @Override
    public List<Store> queryBysname(String sname, Pager<Store> page) {
        List<Store> list = new ArrayList<>();
        Store store=null;
        String sql = "SELECT * FROM tab_store where 1=1 ";
        if(sname!=null&&sname!=""){
            sql +="and sname like concat('%',?,'%')";
        }
        sql +=" limit ?,?";
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            conn = DataSourceUtil.getConnection();
            psmt = conn.prepareStatement(sql);
            if(sname!=null&&sname!=""){
                psmt.setString(1,sname);
                psmt.setInt(2,page.getStart());
                psmt.setInt(3,page.getEnd());
            }else{
                psmt.setInt(1,page.getStart());
                psmt.setInt(2,page.getEnd());
            }
            rs = psmt.executeQuery();
            while (rs.next()) {
                store = new Store();
                store.setSid(rs.getInt(1));
                store.setSname(rs.getString(2));
                store.setStype(rs.getString(3));
                store.setSuser(rs.getString(4));
                store.setSpwd(rs.getString(5));
                store.setSaddress(rs.getString(6));
                store.setPhone(rs.getString(7));
                store.setSdate(rs.getString(8));
                list.add(store);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataSourceUtil.releaseResource(rs, psmt, conn);
        }
        return list;
    }

    @Override
    public List<Store> queryBysname(String sname, int sid) {
        List<Store> list = new ArrayList<>();
        Store store=null;
        String sql = "SELECT * FROM tab_store where 1=1 ";
        if(sname!=null&&sname!=""){
            sql +=" and sname like concat('%',?,'%') ";
        }

            sql += "and sid!=? ";

        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            conn = DataSourceUtil.getConnection();
            psmt = conn.prepareStatement(sql);
            if(sname!=null&&sname!=""){
                psmt.setString(1,sname);
                psmt.setInt(2,sid);
            }else{
                psmt.setInt(1,sid);
            }
            rs = psmt.executeQuery();
            while (rs.next()) {
                store = new Store();
                store.setSid(rs.getInt(1));
                store.setSname(rs.getString(2));
                store.setStype(rs.getString(3));
                store.setSuser(rs.getString(4));
                store.setSpwd(rs.getString(5));
                store.setSaddress(rs.getString(6));
                store.setPhone(rs.getString(7));
                store.setSdate(rs.getString(8));
                list.add(store);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataSourceUtil.releaseResource(rs, psmt, conn);
        }
        return list;
    }

    @Override
    public Store queryById(int id) {
        Store store= null;
        String sql = "SELECT * FROM tab_store where sid =?";

        try {
            Connection conn = DataSourceUtil.getConnection();
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setInt(1, id);
            ResultSet rs = psmt.executeQuery();
            if (rs.next()) {
                store = new Store();
                store.setSid(rs.getInt(1));
                store.setSname(rs.getString(2));
                store.setStype(rs.getString(3));
                store.setSuser(rs.getString(4));
                store.setSpwd(rs.getString(5));
                store.setSaddress(rs.getString(6));
                store.setPhone(rs.getString(7));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        }
        return store;
    }

    @Override
    public int update(Store store) {
        String sql = "UPDATE tab_store SET sname = ?,stype=?,saddress=?,phone=? WHERE sid =?";
        int result = -1;
        try {
            Connection conn = DataSourceUtil.getConnection();
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setString(1, store.getSname());
            psmt.setString(2, store.getStype());
            psmt.setString(3, store.getSaddress());
            psmt.setString(4, store.getPhone());
            psmt.setInt(5, store.getSid());
            result = psmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public int deleteStore(int sid) {
        String sql = "delete  from tab_store where sid=?";
        int result = -1;
        try {
            Connection conn = DataSourceUtil.getConnection();
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setInt(1,sid);
            result = psmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
}
